package top.hmtools.services;

import java.io.IOException;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Service;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javafx.fxml.FXMLLoader;
import javafx.scene.Parent;
import javafx.scene.Scene;
import javafx.stage.Stage;
import top.hmtools.beans.DBConnectInfoBean;
import top.hmtools.beans.columnsInfoBeans.ColumnsBaseInfoBean;
import top.hmtools.beans.tableInfoBeans.TableBaseInfoBean;
import top.hmtools.enums.EDBType;
import top.hmtools.io.FileNameTools;
import top.hmtools.model.ResultModel;
import top.hmtools.model.ResultModel.resultStatus;
import top.hmtools.random.RandomStringTools;
import top.hmtools.services.adapter.BaseDBAdapter;

/**
 * 数据库信息管理服务层
 * @author HyboJ
 *
 */
@Service
public class DbManageService extends BaseService{
	
	private Stage stage;
	/**
	 * 打开“新增数据库连接”窗口
	 */
	public void showAddNewDbConnectionWindow(){
		URL url = Thread.currentThread().getContextClassLoader().getResource("viewFxml/AddNewDBConnection.fxml");
		FXMLLoader fxmlLoader = new FXMLLoader(url);
		
		Parent root = null;
		try {
			root = fxmlLoader.load();
		} catch (IOException e) {
			e.printStackTrace();
			logger.error("读取viewFxml/AddNewDBConnection.fxml失败："+e.getMessage(),e);
		}
		Scene scene = new Scene(root);
		
		stage = new Stage();
		stage.setResizable(true);
		stage.setScene(scene);
		stage.setTitle("新增数据库链接");
		stage.show();
	}

	/**
	 * 添加一条信息
	 * @param bean
	 * @return
	 */
	public boolean addOne(DBConnectInfoBean bean) {
		bean.setId(RandomStringTools.getYyyyMMddHHmmssSSSPrifixInteger(6));
		int addOne = this.gmDbConnectInfoDao.addOne(bean);
		if(addOne>0){
			return true;
		}else{
			return false;
		}
	}
	
	/**
	 * 关闭窗口
	 */
	public void closeStage(){
		this.stage.close();
	}

	/**
	 * 测试数据库链接
	 * @param bean
	 * @return
	 */
	public boolean testConnection(DBConnectInfoBean bean) {
		boolean result = false;
		Connection connection = null;
		try {
			DruidDataSourceFactory druidDataSourceFactory = new DruidDataSourceFactory();
			Map<String,String> properties = new HashMap<>();
			properties.put("url", bean.getUrl());
			properties.put("username", bean.getUsername());
			properties.put("password",bean.getPassword());
			String driverClassName = EDBType.getDriverClassNameByTypeCode(bean.getDb_type());
			properties.put("driverClassName", driverClassName);
			
			Class.forName(driverClassName);
		    connection = DriverManager.getConnection(bean.getUrl(),bean.getUsername(),bean.getPassword());
		    
		    PreparedStatement prepareStatement = connection.prepareStatement("SELECT 'x';");
		    ResultSet resultSet = prepareStatement.executeQuery();
		    result = resultSet.next();
		    logger.info("测试数据链接成功："+bean.getUrl());
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("测试数据链接失败："+e.getMessage(),e);
		}finally{
			if(connection != null){
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
					logger.error("关闭数据链接失败："+e.getMessage(),e);
				}
			}
		}
		return result;
	}

	public List<DBConnectInfoBean> getAll() {
		return this.gmDbConnectInfoDao.getAll();
	}

	/**
	 * 根据数据库类型获取数据库链接信息
	 * @param dbType
	 * @return
	 */
	public ResultModel<DBConnectInfoBean> getConncetionsByDBType(int dbType) {
		ResultModel<DBConnectInfoBean> result = new ResultModel<DBConnectInfoBean>(resultStatus.STATUS_FAIL);
		List<DBConnectInfoBean> dbConInfos = this.gmDbConnectInfoDao.getConncetionsByDBType(dbType);
		if(dbConInfos!=null && dbConInfos.size()>0){
			result.setRows(dbConInfos);
			result.setStatusSuccess();
			result.setMessage("获取数据成功");
		}else{
			result.setMessage("获取数据失败");
		}
		return result;
	}

	/**
	 * 根据id，删除一条数据库链接
	 * @param id
	 * @return
	 */
	public ResultModel<String> deleteOneByID(String id) {
		ResultModel<String> result = new ResultModel<String>(resultStatus.STATUS_FAIL);
		int count = this.gmDbConnectInfoDao.delOne(id);
		if(count>0){
			result.setStatusSuccess();
			result.setMessage("删除数据成功");
		}else{
			result.setMessage("删除数据失败");
		}
		return result;
	}

	/**
	 * 修改一条数据库链接信息
	 * @param bean
	 * @return
	 */
	public ResultModel<DBConnectInfoBean> updateOne(DBConnectInfoBean bean) {
		ResultModel<DBConnectInfoBean> result = new ResultModel<DBConnectInfoBean>(resultStatus.STATUS_FAIL);
		int count = this.gmDbConnectInfoDao.updateOne(bean);
		if(count>0){
			result.setStatusSuccess();
			result.setMessage("修改数据成功");
		}else{
			result.setMessage("修改数据失败");
		}
		return result;
	}

	/**
	 * 获取数据库表信息集合
	 * @param dbConn
	 * @return
	 */
	public ResultModel<TableBaseInfoBean> getTables(DBConnectInfoBean dbConn) {
		ResultModel<TableBaseInfoBean> result = new ResultModel<TableBaseInfoBean>(resultStatus.STATUS_FAIL);
		
		String[] adapterBeanNames = this.applicationContext.getBeanNamesForType(BaseDBAdapter.class);
		for(String beanName:adapterBeanNames){
			BaseDBAdapter dbAdapter = this.applicationContext.getBean(beanName, BaseDBAdapter.class);
			int typeCode = dbAdapter.getEDBType().getTypeCode();
			if(typeCode != dbConn.getDb_type()){
				continue;
			}else{
				String basename = FileNameTools.getBaseName(dbConn.getUrl());
				String dbName = basename.split("\\?")[0];
				List<TableBaseInfoBean> tableInfos = dbAdapter.getTableInfos(dbConn, dbName);
				result.setRows(tableInfos);
				result.setStatusSuccess();
				result.setMessage("获取数据成功");
				break;
			}
		}
		
		return result;
	}

	/**
	 * 获取指定数据库链接、表名的所有表字段名称
	 * @param dbConn
	 * @param tableName
	 * @return
	 */
	public ResultModel<ColumnsBaseInfoBean> getColumns(DBConnectInfoBean dbConn,String tableName) {
		ResultModel<ColumnsBaseInfoBean> result = new ResultModel<ColumnsBaseInfoBean>(resultStatus.STATUS_FAIL);

		String[] adapterBeanNames = this.applicationContext.getBeanNamesForType(BaseDBAdapter.class);
		for (String beanName : adapterBeanNames) {
			BaseDBAdapter dbAdapter = this.applicationContext.getBean(beanName, BaseDBAdapter.class);
			int typeCode = dbAdapter.getEDBType().getTypeCode();
			if (typeCode != dbConn.getDb_type()) {
				continue;
			} else {
				String basename = FileNameTools.getBaseName(dbConn.getUrl());
				String dbName = basename.split("\\?")[0];
				List<ColumnsBaseInfoBean> columnInfos = dbAdapter.getColumnInfos(dbConn, dbName, tableName);
				result.setRows(columnInfos);
				result.setStatusSuccess();
				result.setMessage("获取数据成功");
				break;
			}
		}

		return result;
	}

}
